﻿using System;
using System.Web.Services;
using System.Data;
using Npgsql;
using System.Configuration;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{

    private double cantSegundosMovimiento = 120;


    public Service()
    {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public void prueba(int gid)
    {
        DataTable dataTableLineas = devolverInformacionLinea(gid);

        for (int i = 0; i < dataTableLineas.Rows.Count && i < 5; i++)
        {
            DataRow row = dataTableLineas.Rows[i];
            String xPosicion = "" + (double)row["x"];
            xPosicion = xPosicion.Replace(',', '.');
            String yPosicion = "" + (double)row["y"];
            yPosicion = yPosicion.Replace(',', '.');

        }
    }
    [WebMethod]
    public DataTable devolverInformacionLinea(int gid)
    {
        DataTable infoLinea = crearDataTable();
        Random randObj = new Random();
        DataRow row;
        NpgsqlConnection conexion = new NpgsqlConnection(ConfigurationManager.AppSettings["postgisConn"]);
        DataSet myDataSetFecha = new DataSet();
        String queryCantOmnibus = "select count (distinct idomnibus) from posicion_omnibus_util where gid = " + gid;
        DataSet dsCantOmnibus = ejecutarQuery(queryCantOmnibus, "cantOmnibus", conexion);
        DataTable dtCantOmnibus = dsCantOmnibus.Tables["cantOmnibus"];
        long cantOmnibus = (long)dtCantOmnibus.Rows[0][0];

        for (int i = 1; i <= cantOmnibus; i++)
        {
            long seq = nextSeq(i, gid);
            long idVuelta = 1;
            String queryIDVuelta = "select idvuelta from posicion_omnibus_util where gid = " + gid + " and idomnibus = "  + i;
            DataSet dsIDVuelta = ejecutarQuery(queryIDVuelta, "idVuelta", conexion);
            DataTable dtIDVuelta = dsIDVuelta.Tables["idVuelta"];
            idVuelta = (int)dtIDVuelta.Rows[0][0];
            String queryMaxSeq = "select coalesce(max(seq),-1) from posicion_omnibus where gid = " + gid;
            DataSet dsMaxSeq = ejecutarQuery(queryMaxSeq, "maxSeq", conexion);
            DataTable dtMaxSeq = dsMaxSeq.Tables["maxSeq"];
            if ((int) dtMaxSeq.Rows[0][0] != -1)
            {
                int maxSeq = (int)dtMaxSeq.Rows[0][0];

                if (seq >= maxSeq)
                {
                    seq = seq % maxSeq;
                    idVuelta++;
                }

                String queryXY = "select p.x,p.y,l.nombre from posicion_omnibus p, lineas l where p.gid = l.gid and p.seq = " + seq + " and l.gid = " + gid;
                DataSet dsXY = ejecutarQuery(queryXY, "xy", conexion);
                DataTable dtXY = dsXY.Tables["xy"];
                double x = (double)dtXY.Rows[0][0];
                double y = (double)dtXY.Rows[0][1];
                String nombre = (String)dtXY.Rows[0][2];
                row = infoLinea.NewRow();
                row["idOmnibus"] = i;
                row["x"] = x;
                row["y"] = y;
                row["empresa"] = 20;
                row["linea"] = nombre;
                row["codLinea"] = gid;
                row["codSubLinea"] = 1;
                row["subLinea"] = nombre;
                row["sentido"] = "A";
                row["variante"] = 223;
                row["idvuelta"] = idVuelta;
                infoLinea.Rows.Add(row);
                myDataSetFecha.Clear();
                setLastData(seq, i, gid, idVuelta);
            }
        }

        conexion.Close();
        return infoLinea;
    }

    private DataTable crearDataTable()
    {
        DataTable infoLinea = new DataTable("InfoLinea");
        DataColumn column = new DataColumn();
        column.DataType = System.Type.GetType("System.Int32");
        column.AllowDBNull = false;
        column.Caption = "idOmnibus";
        column.ColumnName = "idOmnibus";

        DataColumn column1 = new DataColumn();
        column1.DataType = System.Type.GetType("System.Double");
        column1.AllowDBNull = false;
        column1.Caption = "x";
        column1.ColumnName = "x";

        DataColumn column2 = new DataColumn();
        column2.DataType = System.Type.GetType("System.Double");
        column2.AllowDBNull = false;
        column2.Caption = "y";
        column2.ColumnName = "y";

        DataColumn column3 = new DataColumn();
        column3.DataType = System.Type.GetType("System.Int32");
        column3.AllowDBNull = false;
        column3.Caption = "empresa";
        column3.ColumnName = "empresa";


        DataColumn column4 = new DataColumn();
        column4.DataType = System.Type.GetType("System.String");
        column4.AllowDBNull = false;
        column4.Caption = "linea";
        column4.ColumnName = "linea";

        DataColumn column5 = new DataColumn();
        column5.DataType = System.Type.GetType("System.Int32");
        column5.AllowDBNull = false;
        column5.Caption = "codLinea";
        column5.ColumnName = "codLinea";

        DataColumn column6 = new DataColumn();
        column6.DataType = System.Type.GetType("System.String");
        column6.AllowDBNull = false;
        column6.Caption = "subLinea";
        column6.ColumnName = "subLinea";

        DataColumn column7 = new DataColumn();
        column7.DataType = System.Type.GetType("System.Int32");
        column7.AllowDBNull = false;
        column7.Caption = "codSubLinea";
        column7.ColumnName = "codSubLinea";

        DataColumn column8 = new DataColumn();
        column8.DataType = System.Type.GetType("System.String");
        column8.AllowDBNull = false;
        column8.Caption = "sentido";
        column8.ColumnName = "sentido";

        DataColumn column9 = new DataColumn();
        column9.DataType = System.Type.GetType("System.String");
        column9.AllowDBNull = false;
        column9.Caption = "variante";
        column9.ColumnName = "variante";

        DataColumn column10 = new DataColumn();
        column10.DataType = System.Type.GetType("System.Int32");
        column10.AllowDBNull = false;
        column10.Caption = "idvuelta";
        column10.ColumnName = "idvuelta";

        infoLinea.Columns.Add(column);
        infoLinea.Columns.Add(column1);
        infoLinea.Columns.Add(column2);
        infoLinea.Columns.Add(column3);
        infoLinea.Columns.Add(column4);
        infoLinea.Columns.Add(column5);
        infoLinea.Columns.Add(column6);
        infoLinea.Columns.Add(column7);
        infoLinea.Columns.Add(column8);
        infoLinea.Columns.Add(column9);
        infoLinea.Columns.Add(column10);

        return infoLinea;
    }

    [WebMethod]
    public DataSet devolverSubLineasParada(Int16 idParada, int idLinea)
    {
        Service s = new Service();
        DataTable result = s.devolverInformacionLinea(idLinea);
        String idSublineas = "";
        foreach (DataRow row in result.Rows)
        {
            foreach (DataColumn col in result.Columns)
            {
                Console.WriteLine(row[col]);
                if (col.ColumnName == "codSubLinea")
                {
                    if (idSublineas != "")
                    {
                        idSublineas = idSublineas + "," + row[col];
                    }
                    else
                    {
                        idSublineas = "" + row[col];
                    }
                }
            }
        }

        NpgsqlConnection conexion = new NpgsqlConnection(ConfigurationManager.AppSettings["postgisConn"]);

        NpgsqlCommand rsCommand = conexion.CreateCommand();
        rsCommand.CommandText = "select idSublinea from sublinea_parada where idParada = " + idParada + " and idSublinea IN (" + idSublineas + ")";

        NpgsqlDataAdapter rsDATAadapter = new NpgsqlDataAdapter();
        rsDATAadapter.SelectCommand = rsCommand;
        DataSet myDataSet = new DataSet();

        conexion.Open();
        rsDATAadapter.Fill(myDataSet);
        conexion.Close();
        return myDataSet;
    }

    [WebMethod]
    public void procesarLineas()
    {
        int[] lineStringProcesados;
        double distancia;
        int temp = 0;
        double distMinima;
        NpgsqlConnection conexion = new NpgsqlConnection(ConfigurationManager.AppSettings["postgisConn"]);
        conexion.Open();
        for (int w = 1; w < 278; w++)
        {

            String queryCantLineString = "select numgeometries(the_geom) from lineas where gid = " + w;
            DataSet dsCantLineString = ejecutarQuery(queryCantLineString, "cantLineString", conexion);
            DataTable dt = dsCantLineString.Tables["cantLineString"];
            int cantLineString = (int)dt.Rows[0][0];
            lineStringProcesados = new int[cantLineString];

            String queryPtoInicial = "select p.x,p.y from paradas p, lineas l where l.id = p.bus and p.id1 = 1 and l.gid = " + w;
            DataSet dsQueryPtoInicial = ejecutarQuery(queryPtoInicial, "ptoInicial", conexion);
            DataTable dtPtoInicial = dsQueryPtoInicial.Tables["ptoInicial"];
            if (dtPtoInicial != null && dtPtoInicial.Rows.Count > 0)
            {
                double xPto = (double)dtPtoInicial.Rows[0][0];
                double yPto = (double)dtPtoInicial.Rows[0][1];
                String xPtoString = "" + xPto;
                String yPtoString = "" + yPto;
                xPtoString = xPtoString.Replace(',', '.');
                yPtoString = yPtoString.Replace(',', '.');

                String puntoInicio = "POINT(" + xPtoString + " " + yPtoString + ")";

                for (int x = 0; x < lineStringProcesados.Length; x++)
                {
                    lineStringProcesados[x] = 0;
                }
                DataSet myDataSetDistancia = new DataSet();
                DataSet myDataSetLineaCercana = new DataSet();
                DataSet myDataSetNuevoPunto = new DataSet();

                for (int i = 1; i < cantLineString; i++)
                {
                    distMinima = 10000;
                    for (int j = 1; j < cantLineString; j++)
                    {   // Eligo el punto mas cercano al punto que estoy procesando
                        String queryDistancia = "select distance(GeomFromText('" + puntoInicio + "'),GeometryN(the_geom," + j + ")) from lineas where gid = " + w;
                        DataSet dsDistancia = ejecutarQuery(queryDistancia, "distancia", conexion);
                        DataTable dtDistancia = dsDistancia.Tables["distancia"];
                        distancia = (double)dtDistancia.Rows[0][0];
                        if (distancia < distMinima && distancia > 0 && lineStringProcesados[j] != 1)
                        {
                            distMinima = distancia;
                            temp = j;
                        }
                        myDataSetDistancia.Clear();
                    }
                    lineStringProcesados[temp] = 1;

                    // Selecciono el punto mas cercano y lo inserto dentro del camino

                    /*NpgsqlCommand rsCommandLineStringCercana = conexion.CreateCommand();
                    rsCommandLineStringCercana.CommandText = "select astext(GeometryN(the_geom," + temp + ")) from lineas where linea = '" + linea + "'";
                    NpgsqlDataAdapter rsDATAadapterLineaCercana = new NpgsqlDataAdapter();
                    rsDATAadapterLineaCercana.SelectCommand = rsCommandLineStringCercana;
                    rsDATAadapterLineaCercana.Fill(myDataSetLineaCercana, "lineaCercana");
                    dt = myDataSetLineaCercana.Tables["lineaCercana"]; */
                    //////////////////
                    //camino = camino + (String)dt.Rows[0][0];
                    //////////////////

                    String queryNuevoPunto = "select X(PointN(GeometryN(the_geom," + temp + "),NPoints(GeometryN(the_geom," + temp + ")))),Y(PointN(GeometryN(the_geom," + temp + "),NPoints(GeometryN(the_geom," + temp + "))))  from lineas where gid = '" + w + "'";
                    DataSet dsNuevoPunto = ejecutarQuery(queryNuevoPunto, "nuevoPunto", conexion);
                    DataTable dtNuevoPunto = dsNuevoPunto.Tables["nuevoPunto"];

                    double x = (double)dtNuevoPunto.Rows[0][0];
                    double y = (double)dtNuevoPunto.Rows[0][1];

                    xPtoString = "" + x;
                    yPtoString = "" + y;
                    xPtoString = xPtoString.Replace(',', '.');
                    yPtoString = yPtoString.Replace(',', '.');


                    NpgsqlCommand rsCommandPuntoInsert = conexion.CreateCommand();
                    rsCommandPuntoInsert.CommandText = "INSERT INTO posicion_omnibus_nueva(x, y, seq, gid) VALUES (" + xPtoString + "," + yPtoString + "," + i + "," + w + ")";
                    rsCommandPuntoInsert.ExecuteNonQuery();
                    myDataSetLineaCercana.Clear();
                    myDataSetNuevoPunto.Clear();
                }
            }
        }
    }

    private DataSet ejecutarQuery(String query, String nmTabla, NpgsqlConnection conexion)
    {
        DataSet myDataSet = new DataSet();
        NpgsqlCommand rsCommandDistancia = conexion.CreateCommand();
        rsCommandDistancia.CommandText = query;
        NpgsqlDataAdapter rsDATAadapterDistancia = new NpgsqlDataAdapter();
        rsDATAadapterDistancia.SelectCommand = rsCommandDistancia;
        rsDATAadapterDistancia.Fill(myDataSet, nmTabla);
        return myDataSet;
    }

    [WebMethod]
    public String devolverCaminoPuntos(String linea)
    {
        double distMinima;
        double distancia;
        String camino = "";
        int[] lineStringProcesados;
        int temp = 0;
        /* Cantidad de LINESTRING de la MULTILINESTRING que estoy procesando*/
        int cantLineStringInMultiLineString = 33;

        String puntoInicio = "POINT(478090.781337093 6140267.5000487)";

        lineStringProcesados = new int[cantLineStringInMultiLineString];
        NpgsqlConnection conexion = new NpgsqlConnection(ConfigurationManager.AppSettings["postgisConn"]);
        DataSet myDataSetDistancia = new DataSet();
        DataSet myDataSetLineaCercana = new DataSet();
        DataSet myDataSetNuevoPunto = new DataSet();
        DataTable dt = null;
        conexion.Open();

        for (int x = 0; x < lineStringProcesados.Length; x++)
        {
            lineStringProcesados[x] = 0;
        }

        for (int i = 1; i < cantLineStringInMultiLineString; i++)
        {
            distMinima = 10000;
            for (int j = 1; j < cantLineStringInMultiLineString; j++)
            {   // Eligo el punto mas cercano al punto que estoy procesando
                NpgsqlCommand rsCommandDistancia = conexion.CreateCommand();
                rsCommandDistancia.CommandText = "select distance(GeomFromText('" + puntoInicio + "'),GeometryN(the_geom," + j + ")) from lineas where linea = '" + linea + "'";
                NpgsqlDataAdapter rsDATAadapterDistancia = new NpgsqlDataAdapter();
                rsDATAadapterDistancia.SelectCommand = rsCommandDistancia;
                rsDATAadapterDistancia.Fill(myDataSetDistancia, "distancia");
                dt = myDataSetDistancia.Tables["distancia"];
                distancia = (double)dt.Rows[0][0];
                if (distancia < distMinima && distancia > 0 && lineStringProcesados[j] != 1)
                {
                    distMinima = distancia;
                    temp = j;
                }
                myDataSetDistancia.Clear();
            }
            lineStringProcesados[temp] = 1;

            // Selecciono el punto mas cercano y lo inserto dentro del camino

            NpgsqlCommand rsCommandLineStringCercana = conexion.CreateCommand();
            rsCommandLineStringCercana.CommandText = "select astext(GeometryN(the_geom," + temp + ")) from lineas where linea = '" + linea + "'";
            NpgsqlDataAdapter rsDATAadapterLineaCercana = new NpgsqlDataAdapter();
            rsDATAadapterLineaCercana.SelectCommand = rsCommandLineStringCercana;
            rsDATAadapterLineaCercana.Fill(myDataSetLineaCercana, "lineaCercana");
            dt = myDataSetLineaCercana.Tables["lineaCercana"];
            camino = camino + (String)dt.Rows[0][0];


            NpgsqlCommand rsCommandNuevoPunto = conexion.CreateCommand();
            rsCommandNuevoPunto.CommandText = "select X(PointN(GeometryN(the_geom," + temp + "),NPoints(GeometryN(the_geom," + temp + ")))),Y(PointN(GeometryN(the_geom," + temp + "),NPoints(GeometryN(the_geom," + temp + "))))  from lineas where linea = '" + linea + "'";
            NpgsqlDataAdapter rsDATAadapterNuevoPunto = new NpgsqlDataAdapter();
            rsDATAadapterNuevoPunto.SelectCommand = rsCommandNuevoPunto;
            rsDATAadapterNuevoPunto.Fill(myDataSetNuevoPunto, "nuevoPunto");
            dt = myDataSetNuevoPunto.Tables["nuevoPunto"];
            double x = (double)dt.Rows[0][0];
            double y = (double)dt.Rows[0][1];
            puntoInicio = "POINT(" + x + " " + y + ")";
            puntoInicio = puntoInicio.Replace(",", ".");
            myDataSetLineaCercana.Clear();
            myDataSetNuevoPunto.Clear();
        }
        return camino;
    }


    [WebMethod]
    public void insertarPuntos()
    {
        int cantPuntosEnLineString = 251;
        NpgsqlConnection conexion = new NpgsqlConnection(ConfigurationManager.AppSettings["postgisConn"]);
        DataSet myDataSetPunto = new DataSet();
        DataTable dt = null;
        conexion.Open();

        for (int i = 1; i < cantPuntosEnLineString; i++)
        {
            NpgsqlCommand rsCommandPunto = conexion.CreateCommand();
            rsCommandPunto.CommandText = "select X(astext(PointN(roads_geom," + i + "))),Y(astext(PointN(roads_geom," + i + "))) from roads";
            NpgsqlDataAdapter rsDATAadapterPunto = new NpgsqlDataAdapter();
            rsDATAadapterPunto.SelectCommand = rsCommandPunto;
            rsDATAadapterPunto.Fill(myDataSetPunto, "punto");
            dt = myDataSetPunto.Tables["punto"];
            double x = (double)dt.Rows[0][0];
            double y = (double)dt.Rows[0][1];
            String xString = "" + x;
            String yString = "" + y;
            xString = xString.Replace(",", ".");
            yString = yString.Replace(",", ".");

            NpgsqlCommand rsCommandPuntoInsert = conexion.CreateCommand();
            rsCommandPuntoInsert.CommandText = "INSERT INTO posicion_omnibus(x, y, seq, linea) VALUES (" + xString + "," + yString + "," + i + ",'D11');";
            rsCommandPuntoInsert.ExecuteNonQuery();
            myDataSetPunto.Clear();
        }

    }

    private long nextSeq(int idOmnibus, int gid)
    {
        NpgsqlConnection conexion = new NpgsqlConnection(ConfigurationManager.AppSettings["postgisConn"]);
        conexion.Open();
        String currentTimeStamp = "select current_timestamp";
        DataSet dsCurrentTS = ejecutarQuery(currentTimeStamp, "currentTS", conexion);
        DataTable dtCurrentTS = dsCurrentTS.Tables["currentTS"];
        System.DateTime dtActual = (System.DateTime)dtCurrentTS.Rows[0][0];
        
        String ultimoTS = "select fecha,ultimo_seq from posicion_omnibus_util where idomnibus=" + idOmnibus + " and gid=" + gid;
        DataSet dsUltimoTS = ejecutarQuery(ultimoTS, "ultimoTS", conexion);
        DataTable dtUltimoTS = dsUltimoTS.Tables["ultimoTS"];
        System.DateTime dtUltima = (System.DateTime)dtUltimoTS.Rows[0][0];
        int ultimoSeq = (int)dtUltimoTS.Rows[0][1];
        
        System.TimeSpan diffResult = dtActual.Subtract(dtUltima);
        double cantSegundos = diffResult.TotalSeconds;
        double sumando =  (cantSegundos / cantSegundosMovimiento);
        conexion.Close();
        long result = (long)(ultimoSeq + sumando);
        return result;
    }

    private void setLastData(long seq, int idOmnibus, int gid, long idVuelta)
    {
        NpgsqlConnection conexion = new NpgsqlConnection(ConfigurationManager.AppSettings["postgisConn"]);
        conexion.Open();
        NpgsqlCommand rsCommandPuntoUpdate = conexion.CreateCommand();
        rsCommandPuntoUpdate.CommandText = "UPDATE posicion_omnibus_util SET fecha=current_timestamp, idvuelta=" + idVuelta + ", ultimo_seq=" + seq + " WHERE idomnibus = " + idOmnibus + " and gid = " + gid + " and ultimo_seq !=" + seq;
        rsCommandPuntoUpdate.ExecuteNonQuery();
        conexion.Close();
    }



}
